#!/opt/perl58/bin/perl

use strict;
use DBI;
use Switch;

#	created by Ken Parisi
#	kenparisi@gmail.com
#	kparisi@sussex.edu

#SQL vars
my($sth, $STMT, @results);

my($i,$s,$from); 
#$i is used in the while statements for accessing elements of arrays 
#$s is an escape variable used in creating course lines that will quit an entire line if duplicate key exists
#$from is used for predefined areas of the script that subs need to know about

#DB CONNECT
#connect attribs
my (%dbattr) = (
    ChopBlanks => 1,    # Remove trailing white-space from columns
    RaiseError => 0     # Automatically output any errors
);

# Open CX Informix database connection & output handlers
my $db1 = DBI->connect("dbi:Informix:cars", '', '', \%dbattr);

#Establish session from system, or if argument is given, use that
my($date,$yr,$month,$day,$sess,$filesess);
$yr = `date +%Y`;
chomp $yr;
$date = `date +%m/%d/%Y`;
chomp $date;
if(!($ARGV[0]))
{
	$month = `date +%m`;
	chomp $month;
	$day = `date +%d`;
	chomp $day;
	$sess;
	if($month>=1){$sess="'WI','AW','SP','AS'"; $filesess="SPRINTER";}
	if($month>=4){$sess="'SP','AS','SW','S1','S2','S3'"; $filesess="SAPSAW";}
	if($month>=5){$sess="'S1','S2','S3','AU','SU','SW','FA','AF'"; $filesess="FALMER";}
	if($month>=8){$sess="'FA','AF'"; $filesess="FALL";}
	if($month>=11){$sess="'WI','AW','SP','AS'"; $filesess="SPRINTER"; $yr++;}
}
else{$sess="'".$ARGV[0]."'"; $filesess=$sess;}

#print vars
my $output = 1; #0=screen  1=file write
my ($rowfetch, $fh, $print_flag, $stat_file, $fileprefix, $filesuffix);
my $stat_dir = "/home/carsids/kparisi/angel/output/online/";
my $fileextension = ".csv";

#Clear the previous csv files before creating new ones.
system("rm  /home/carsids/kparisi/angel/output/online/*.csv");

##############################################################################################################
############												COURSES 												############
##############################################################################################################

$from=1; #used in subroutines to determine which of the 3 csv files to process

#Columns for import to COURSES in Angel
my (@courses_col);
push(@courses_col,"course_id","course_type","category","redirect_url","redirect_target","enrollment_method","enrollment_pin","stylesheet");
push(@courses_col,"instructor_id","instructor","title","keywords","semester","campus","school","department","course","section","cost","credits");
push(@courses_col,"contact1","contact2","enrollment_begins","enrollment_ends","class_begins","class_ends","hidden","locked","disabled");
push(@courses_col,"viewable_by","billing_id","description","container_id","course_time","duration","schedule_type","schedule_days","schedule_interval");
push(@courses_col,"schedule_occurrence","course_upload_url","isdeleted","sourceid");

#loop vars
my ($courses_col_len,$crs_rows,@dupecatch);
$courses_col_len = @courses_col;

#Get course data, either active or inactive
$STMT = <<EOF;
SELECT DISTINCT		TRIM(sec_rec.sess) sess, 
					TRIM(crs_rec.site) site,
					TRIM(SUBSTR(sec_rec.crs_no,0,3)) dept, 
					TRIM(sec_rec.crs_no) crs, 
					TRIM(sec_rec.sec_no) sec,
					secmtg_rec.cat CATEGORY,
					TRIM((TRIM(crs_rec.title1)||" "||TRIM(crs_rec.title2)||" "||TRIM(crs_rec.title3))) TITLE,
					SUBSTR(sec_rec.yr,3,2) year,
					id_rec.fullname,
					sec_rec.beg_date,
					sec_rec.end_date,
					sec_rec.hrs,
					sec_rec.fac_id,
					sec_rec.stat
FROM				sec_rec, 
					crs_rec,
					id_rec,
					outer secmtg_rec
WHERE				sec_rec.crs_no = crs_rec.crs_no
					AND sec_rec.fac_id = id_rec.id
					AND sec_rec.yr = $yr
					AND crs_rec.cat = sec_rec.cat
					AND sec_rec.sess IN ($sess)
					AND secmtg_rec.crs_no = sec_rec.crs_no
					AND secmtg_rec.cat = sec_rec.cat
					AND secmtg_rec.yr = sec_rec.yr
					AND secmtg_rec.sess = sec_rec.sess
					AND secmtg_rec.sec_no = sec_rec.sec_no
					{AND ((sec_rec.sec_no LIKE '%O%' OR sec_rec.sec_no LIKE '%H%') AND sec_rec.sec_no NOT LIKE 'OL')}
INTO 				TEMP angel_crs WITH NO LOG;
EOF
$sth = $db1->prepare($STMT);
$sth->execute();

#Model data fields after those in courses table on Angel
$STMT = <<EOF;
SELECT DISTINCT		(sess||year||"-"||site||"-"||dept||"-"||crs||"-"||sec) COURSE_ID
FROM				angel_crs
WHERE				stat = 'X'
					OR fac_id IN (3,287,9238,59999)
					OR fullname NOT LIKE '%,%'
INTO				TEMP dirty_sections WITH NO LOG;
EOF
$sth = $db1->prepare($STMT);
$sth->execute();

#Model data fields after those in courses table on Angel
$STMT = <<EOF;
SELECT DISTINCT		(sess||year||"-"||site||"-"||dept||"-"||crs||"-"||sec) COURSE_ID,
					"COURSE" COURSE_TYPE,
					CATEGORY,
					"" REDIRECT_URL,
					"" REDIRECT_TARGET,
					"" ENROLLMENT_METHOD,
					"" ENROLLMENT_PIN,
					"" STYLESHEET,
					fac_id INSTRUCTOR_ID,
					fullname INSTRUCTOR,
					TRIM((crs||" - "||sec||" - "||TRIM(TITLE)||" - "||sess||year)) TITLE, 
					TRIM((TRIM(TITLE)||", "||dept||", "||crs||", "||sec||", "||fullname)) KEYWORDS,
					TRIM((sess||year)) SEMESTER,
					site CAMPUS,
					site SCHOOL,
					dept DEPARTMENT,
					crs COURSE,
					sec SECTION,
					"" COST,
					hrs CREDITS,
					fullname CONTACT1,
					"" CONTACT2,
					("01/01/2000 0:01") ENROLLMENT_BEGINS,
					("01/01/2000 0:01") ENROLLMENT_ENDS,
					(beg_date||" 0:01") CLASS_BEGINS,
					(end_date+7||" 23:59") CLASS_ENDS,
					stat HIDDEN,
					stat LOCKED,
					stat DISABLED,
					stat VIEWABLE_BY,
					"XEI" BILLING_ID,
					(crs||" - "||sec||" - "||TRIM(TITLE)||" - "||sess||year) DESCRIPTION,
					"" CONTAINER_ID,
					"" COURSE_TIME,
					"0" DURATION,
					"0" SCHEDULE_TYPE,
					"0" SCHEDULE_DAYS,
					"0" SCHEDULE_INTERVAL,
					"0" SCHEDULE_OCCURRENCE,
					"" COURSE_UPLOAD_URL,
					"False" IsDeleted,
					(sess||year||"-"||site||"-"||dept||"-"||crs||"-"||sec) SourceId	
FROM				angel_crs
WHERE				fac_id NOT IN (3,287,9238,59999) {Solweig Dimino & generic staff id# filtered out}
					AND fac_id NOT IN (select id from id_rec where fullname not like '%,%')
EOF
$sth = $db1->prepare($STMT);
$sth->execute();

$fileprefix = "ANGEL_".$filesess.substr($yr, 2, 2)."_COURSES";
openfile(); #Open csv for writing
writeheadings($courses_col_len); #fill column headings based on array defined above

#Create CSV lines
$i=0;	
$crs_rows=0;
while(@results=$sth->fetchrow_array())
{
	$s=0; #s keeps track of output conditions for end of row
	while ($i != $courses_col_len and $s==0)
	{
		$rowfetch = $results[$i];
		if($i==0){$dupecatch[1] = $rowfetch;} #dupecatch prevents duplicate primary key field entries
		if($dupecatch[0] ne $dupecatch[1]){course_printline();}else{$s++;} #if first field (primary key) is not a duplicate, write the line, else increment s to escape row
		$i++;
	}
	if($i==$courses_col_len){$crs_rows++;} #keeps track of total rows written (for reporting in cron messages)
	$i=0;
	$dupecatch[0]=$dupecatch[1]; #move the old primary key to different element, new one will be loaded in element 1
	if($s==0){print $fh "\n";} #end of row handling, if the row was escaped, no need to print carriage return
}

##############################################################################################################
############												FACULTY 												############
##############################################################################################################

$from=2; #used in subroutines to determine which of the 3 csv files to process

#Columns for import to COURSE_ROSTER in Angel
my ($fac_rows,@fac_roster_col);
push(@fac_roster_col,"source_id","first_name","last_name","email","login_name","password","account_rights","course_id","course_rights","title","team","enrollment_action");
my $fac_roster_len = @fac_roster_col;

#Get faculty data
$STMT = <<EOF;
SELECT DISTINCT		TRIM(sec_rec.sess) sess, 
					TRIM(crs_rec.site) site, 
					TRIM(SUBSTR(sec_rec.crs_no,0,3)) dept, 
					TRIM(sec_rec.crs_no) crs, 
					TRIM(sec_rec.sec_no) sec,
					SUBSTR(sec_rec.yr,3,2) year,
					id_rec.fullname,
					id_rec.id,
					aa_rec.line1 email,
					sec_rec.stat
FROM				sec_rec, 
					crs_rec,
					id_rec,
					outer aa_rec
WHERE				sec_rec.crs_no = crs_rec.crs_no
					AND sec_rec.fac_id = id_rec.id
					AND sec_rec.yr = $yr
					AND crs_rec.cat = sec_rec.cat
					AND sec_rec.sess IN ($sess)
					AND sec_rec.fac_id = aa_rec.id
					AND aa_rec.aa = "EML"
					{AND ((sec_rec.sec_no LIKE '%O%' OR sec_rec.sec_no LIKE '%H%') AND sec_rec.sec_no NOT LIKE 'OL')}
INTO 				TEMP angel_fac WITH NO LOG;
EOF
$sth = $db1->prepare($STMT);
$sth->execute();

#Model data fields after those needed in batch enrollmentl
$STMT = <<EOF;
SELECT DISTINCT		id SOURCE_ID,
					fullname FIRST_NAME,
					fullname LAST_NAME,
					email EMAIL,
					fullname LOGIN_NAME,
					"sussex" PASSWORD,
					"8" ACCOUNT_RIGHTS,
					(sess||year||"-"||site||"-"||dept||"-"||crs||"-"||sec) COURSE_ID,
					"64" COURSE_RIGHTS,
					"Instructor" TITLE,
					"Faculty" TEAM,
					stat ENROLLMENT_ACTION
FROM				angel_fac
WHERE				id NOT IN (3,287,9238,52601,59999) {Solweig Dimino & generic staff id# filtered out}
					AND (sess||year||"-"||site||"-"||dept||"-"||crs||"-"||sec) NOT IN (SELECT course_id FROM dirty_sections)
EOF
$sth = $db1->prepare($STMT);
$sth->execute();

$fileprefix = "ANGEL_".$filesess.substr($yr, 2, 2)."_FACULTY";
openfile(); #Open csv for writing
writeheadings($fac_roster_len); #fill column headings based on array defined above

#Create CSV faculty import lines
$i=0;
$fac_rows=0;
while(@results=$sth->fetchrow_array())
{
	while ($i != $fac_roster_len)
	{
		$rowfetch = $results[$i];
		switch ($i) 
		{
			case [1,2,4] {regex_printline();} #first name, last name, and login name all need to be regexed
			case 11 {if($rowfetch eq 'X'){$rowfetch=1; printline();} else{$rowfetch=0; printline();}} #if stat = X, section was cancelled
			else{printline();}
		}
		$i++;
	}
	$i=0;
	$fac_rows++; #counts total rows created for reporting in the cron output
	print $fh "\n"; #add carriage return before starting next row
}

##############################################################################################################
############												STUDENTS												############
##############################################################################################################

$from=3; #used in subroutines to determine which of the 3 csv files to process

#Columns for import to COURSE_ROSTER in Angel
my ($stu_rows,@stu_roster_col);
push(@stu_roster_col,"source_id","first_name","last_name","email","login_name","password","account_rights","course_id","course_rights","title","team","enrollment_action");
my $stu_roster_len = @stu_roster_col;

#Get student enrollment data
$STMT = <<EOF;
SELECT DISTINCT		TRIM(cw_rec.sess) sess, 
					TRIM(cw_rec.site) site, 
					TRIM(SUBSTR(crs_rec.crs_no,0,3)) dept, 
					TRIM(cw_rec.crs_no) crs, 
					TRIM(cw_rec.sec) sec,
					SUBSTR(cw_rec.yr,3,2) year,
					id_rec.id,
					id_rec.fullname,
					aa_rec.line1,
					cw_rec.stat,
					cw_rec.cw_no
FROM				cw_rec,
					id_rec,
					crs_rec,
					sec_rec,
					outer aa_rec
WHERE				cw_rec.id = id_rec.id
					AND cw_rec.crs_no = crs_rec.crs_no
					AND cw_rec.yr = $yr
					AND cw_rec.sess IN ($sess)
					AND crs_rec.cat = cw_rec.cat
					AND aa_rec.id = id_rec.id
					AND aa_rec.aa = "EML"
					AND cw_rec.crs_no = sec_rec.crs_no
					AND cw_rec.sec = sec_rec.sec_no
					AND sec_rec.fac_id NOT IN (3,287,9238,52601,59999)
					AND cw_rec.id NOT IN (3,287,9238,52601,59999)
					AND cw_rec.id NOT IN (SELECT id FROM id_rec WHERE fullname LIKE 'Doe, J%')
					{AND ((sec_rec.sec_no LIKE '%O%' OR sec_rec.sec_no LIKE '%H%') AND sec_rec.sec_no NOT LIKE 'OL')}
INTO				TEMP angel_stu WITH NO LOG;
EOF
$sth = $db1->prepare($STMT);
$sth->execute();

#Get rid of any registration mistakes (added 4/06/10 to resolve issues of students staying on teams)
#if cw_no for a drop/withdraw is before an add/register, student is deleted from course, then added back, this will destroy relationships in Angel courses like team assignments
$STMT = <<EOF;
SELECT				(id||sess||year||crs||sec) cid, 
					MAX(cw_no) max_cw
FROM				angel_stu 
GROUP BY			id, 
					crs, 
					year, 
					sess, 
					sec 
HAVING				COUNT(cw_no) > 1 
UNION
SELECT				(id||sess||year||crs||sec) cid, 
					MAX(cw_no) max_cw
FROM				angel_stu 
GROUP BY			id, 
					crs, 
					year, 
					sess, 
					sec 
HAVING				COUNT(cw_no) = 1
INTO				TEMP reg_mistakes WITH NO LOG;
EOF
$sth = $db1->prepare($STMT);
$sth->execute();

$STMT = <<EOF;
DELETE FROM			angel_stu 
WHERE				(id||sess||year||crs||sec||cw_no) NOT IN (SELECT (cid||max_cw) FROM reg_mistakes);
EOF
$sth = $db1->prepare($STMT);
$sth->execute();

#Model data fields after those needed in batch enrollment
$STMT = <<EOF;
SELECT DISTINCT		id SOURCE_ID,
					fullname FIRST_NAME,
					fullname LAST_NAME,
					line1 EMAIL,
					id LOGIN_NAME,
					"sussex" PASSWORD,
					"2" ACCOUNT_RIGHTS,
					(sess||year||"-"||site||"-"||dept||"-"||crs||"-"||sec) COURSE_ID,
					"2" COURSE_RIGHTS,
					"Student" TITLE,
					"Students" TEAM,
					stat ENROLLMENT_ACTION,
					cw_no
FROM				angel_stu
WHERE				(sess||year||"-"||site||"-"||dept||"-"||crs||"-"||sec) NOT IN (SELECT course_id FROM dirty_sections)
ORDER BY 			cw_no
EOF
$sth = $db1->prepare($STMT);
$sth->execute();

$fileprefix = "ANGEL_".$filesess.substr($yr, 2, 2)."_STUDENTS";
openfile(); #Open csv for writing
writeheadings($stu_roster_len); #fill column headings based on array defined above

#Create CSV stuulty import lines
$i=0;	
$stu_rows=0;
while(@results=$sth->fetchrow_array())
{
	while ($i != $stu_roster_len)
	{
		$rowfetch = $results[$i];
		switch ($i) 
		{
			case [1,2]	{regex_printline($rowfetch);} #first name and last name need to be regexed
			case 11 {if($rowfetch eq 'C' || $rowfetch eq 'R'){$rowfetch=0;printline($rowfetch);}else{$rowfetch=1;printline($rowfetch);}} #stat field determines if student is enrolled or has withdrawn/dropped
			else {printline($rowfetch);}
		}
		$i++;
	}
	$i=0;	
	$stu_rows++; #counts the total rows created for cron reporting 
	print $fh "\n"; #add carriage return before starting new row
}
##############################################################################################################
############										MAKE ENROLLMENT FILES 9/2/2010									############
##############################################################################################################

system("cp /home/carsids/kparisi/angel/output/online/*FACULTY* /home/carsids/kparisi/angel/output/online/fac_enroll.csv");
system("cp /home/carsids/kparisi/angel/output/online/*STUDENTS* /home/carsids/kparisi/angel/output/online/stu_enroll.csv");

##############################################################################################################
############												NOTIFY 												############
##############################################################################################################

#Creates a message for the cron to report back to my email
my $runtime = `date`;
my $du = `du -k /home/carsids/kparisi/angel/output/`; #gets directory size
$sess =~ s/[']//g; #All chars inside the [ ] will be filtered out. - At the top, if one session is selected, it carries the single quotes, they need to be gone
my $message = "Last run:  $runtime\nParameters: $sess $yr\n\nCourses - $crs_rows\nFaculty Enrollments - $fac_rows\nStudent Enrollments - $stu_rows\n\nDir size:  \n$du\n";
print $message;

open(MAIL, "|/usr/sbin/sendmail -t");
print MAIL "To: kparisi\@sussex.edu\n";
print MAIL "From: ANGEL-XEI\n";
print MAIL "Subject: ANGEL XEI - Run Results for $sess $yr\n\n";
print MAIL "$message";
close(MAIL);

##############################################################################################################
############												SFTP  												############
##############################################################################################################

system("sftp -b /home/carsids/kparisi/angel/sftp.xei Sussex_XEI\@sftp.angellearning.com");

##############################################################################################################
############												SUBS 												############
##############################################################################################################

#Opens CSV file to write to and controls auto-numbering depending on what is already in the folder it is writing to
sub openfile
{
	#Open file for writing
	if ($output==0) #debug mode
	{
		$fh = \*STDOUT;
		$print_flag = ( $output++ == 1 );
	}
	else
	{
		$filesuffix = 0; $fileprefix =~ s/[']//g; #All chars inside the [ ] will be filtered out. - At the top, if one session is selected, it carries the single quotes, they need to be gone
		$stat_file = $stat_dir."/".$fileprefix.$filesuffix.$fileextension;
		while (-e $stat_file) {$stat_file = $stat_dir."/".$fileprefix . ++$filesuffix . $fileextension;} #auto numbering revision handling in case files were not deleted
		$fh = \*STAT_FILE;	open ($fh,">>".$stat_file) or die "Can't open $stat_file: $!\n";
		$print_flag = ( -z $stat_file );
	}
}

#Writes the column headings into the CSV files
sub writeheadings
{
	my $col_len = shift; #takes argument from function call
	$i=0;
	while ($i != $col_len)
	{ 
		#"from" constants are defined before each of the 3 main sections
		switch ($from)
		{
			case 1 {print $fh '"'.$courses_col[$i].'"';}
			case 2 {print $fh '"'.$fac_roster_col[$i].'"';}
			case 3 {print $fh '"'.$stu_roster_col[$i].'"';}
			else {exit;} #if from is something other than 1,2, or 3 - make bail, something went wrong
		}
		if($i!=($col_len-1)){print $fh ",";} #if not the last element, add a comma
		$i++;
	}
	print $fh "\n"; #add carriage return before starting next row (which at this point is the sql csv lines)
}

#Handles the parsing of data coming from the COURSES section
sub course_printline
{
	$rowfetch =~ s/[']//g; #All chars inside the [ ] will be filtered out.
	if($i==9 || $i==20) #instructor name gets regexed
	{
		my ($lname, $fname) = split(/,\s*/, $rowfetch, 2); #creates a first and last name from the fullname field
		$rowfetch = $fname . " " . $lname;
	}
	if($i==19){$rowfetch = substr($rowfetch, 0, 1);}
	if($i>=26 && $i<=29) #hidden, locked, disabled, viewable_by from angel are all determined by the section stat
	{
		#if section is cancelled, check which field is coming in
		#set hidden to 1 ( 1 = prevents course from showing in searches)
		#set locked to 1 (field is not used by angel, so '1' makes it easy to filter in sql on angel)
		#set disabled to 1 (1 = prevents course access, removes course from searches)
		#set viewable_by to 64 so that only instructors can see course
			#else course is open, set all to 0
		if($rowfetch eq "X"){$rowfetch=1;if($i==27){$rowfetch=1;}if($i==29){$rowfetch=64;}}
		else{$rowfetch=0;}
	}
	
	print $fh '"'.$rowfetch.'"';
	if($i!=($courses_col_len-1)){print $fh ",";} #if not last element in row, add comma
}

#Regex to split the fullname field into either a first and a last name or a login name ex: kparisi
sub regex_printline
{
	$rowfetch =~ s/[']//g; #All chars inside the [ ] will be filtered out.
	if($i==1 || $i==2) #if field is first name or last name
	{
		my($lname, $fname) = split /,\s*/, $rowfetch; #creates a first and last name from the fullname field
		if($i==1){$rowfetch=$fname;}else{$rowfetch=$lname;} #handles which variable to pick & print from the regex split
	}
	else
	{ #turns fullname field into login name  ex: Parisi, Kenneth = kparisi
		$rowfetch =~ s/[-]//g; #All chars inside the [ ] will be filtered out.
		$rowfetch =~ m/(\w+), ?(.)/; #Don't really understand how this works, but it sure does
		$rowfetch = lc($2.$1); #rearranges the split done by regex and lowercases the chars
	}
	printline();
}

#Generic print statements to encapsulate data in quotes and conditionals for handling end-of-row activity
sub printline
{
	print $fh '"'.$rowfetch.'"';
	#"from" constants are defined before each of the 3 main sections
	switch ($from)
	{
		case 1 {if($i!=($courses_col_len-1)){print $fh ",";}} #if not last element in row, add comma}
		case 2 {if($i!=($fac_roster_len-1)){print $fh ",";}} #if not last element in row, add comma}
		case 3 {if($i!=($stu_roster_len-1)){print $fh ",";}} #if not last element in row, add comma}
		else{exit;} #if from is something other than 1,2, or 3 - make bail, something went wrong
	}	
}

exit;